﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using DAL;
using Common;

namespace BLL
{
    public class EmployeeManager
    {
        private static EFHelper<OASystemEntities> _efHelper = new EFHelper<OASystemEntities>();


        #region 员工列表的分页
        /// <summary>
        /// 员工列表的分页
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNo"></param>
        /// <returns></returns>
        public List<OA_UserAccount> QueryPageList(int pageSize, int pageNo, string keyword)
        {
            try
            {

                
                var  userAccountList = _efHelper.QueryList<OA_UserAccount>(null).OrderByDescending(u => u.UpdateTime).ToList(); 
                  new RedisHelper().SetValue<List<OA_UserAccount>>("user",userAccountList,DateTime.Now.AddSeconds(30))

                 
                if (keyword != "" && keyword != null)
                    userAccountList.Where(u => u.Account.Contains(keyword)).ToList();

                
                return userAccountList.Take(pageSize).Skip((pageNo - 1) * pageSize).ToList();

            }
            catch (Exception)
            {

                throw;
            }


        }
        #endregion


        #region 根据员工ID查询返回员工基本信息
        /// <summary>
        /// 根据员工ID查询返回员工基本信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static OA_UserBasic GetUserBasic(int userId)
        {
            try
            {
                return _efHelper.GetInfo<OA_UserBasic>(ub => ub.UserId == userId);
            }
            catch (Exception ex)
            {
                return new OA_UserBasic();
            }
        }
        #endregion

        #region 根据ID查询返回员工账号表信息
        /// <summary>
        ///根据ID查询返回员工账号表信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static OA_UserAccount GetUserAccount(int Id)
        {
            try
            {
                return _efHelper.GetInfo<OA_UserAccount>(u => u.Id == Id);
            }
            catch (Exception ex)
            {
                return new OA_UserAccount();
            }
        }
        #endregion

        public List<UserInfo> QueryList()
        {
            try
            {

                var userAccountList = _efHelper.QueryList<OA_UserAccount>(null).OrderBy(u => u.UpdateTime).ToList();
                var userBasicList = _efHelper.QueryList<OA_UserBasic>(null).OrderBy(u => u.UpdateTime).ToList();

                var query = from a in userAccountList
                            join b in userBasicList on a.Id equals b.UserId
                            select new UserInfo
                            {
                                Id = a.Id,
                                Account = a.Account,
                                RealName = b.RealName,
                                Sex = b.Sex,
                                IDCard = b.IDCard,
                                Address1 = b.Address1,
                                Address2 = b.Address2,
                                Brithday = b.Brithday,
                                PositionName = _efHelper.GetInfo<OA_Position>(p => p.Id == b.PositionId) == null ? "" : _efHelper.GetInfo<OA_Position>(p => p.Id == b.PositionId).Name,
                                DepartmentName = _efHelper.GetInfo<OA_Department>(p => p.Id == b.DepartmentId) == null ? "" : _efHelper.GetInfo<OA_Department>(p => p.Id == b.DepartmentId).Name
                            };
                return query.ToList<UserInfo>();

            }
            catch (Exception)
            {

                throw;
            }


        }


        #region 录入员工信息
        /// <summary>
        /// 录入员工信息
        /// </summary>
        /// <param name="account"></param>
        /// <param name="userBasic"></param>
        /// <returns></returns>
        public bool AddEmployee(OA_UserAccount account, OA_UserBasic userBasic)
        {
            try
            {

                int num = _efHelper.Insert<OA_UserAccount>(account);

                userBasic.UserId = account.Id;

                int row = _efHelper.Insert<OA_UserBasic>(userBasic);

                if (num >= 1 && row >= 1)
                    return true;
                else
                    return false;

            }
            catch (Exception ex)
            {
                return false;
            }

        }
        #endregion


        #region 切换员工账号
        /// <summary>
        /// 切换员工账号
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public int UpdateEmpStatus(int Id, bool result)
        {

            try
            {
                var userAccount = _efHelper.GetInfo<OA_UserAccount>(u => u.Id == Id);
                var num = 0;
                if (userAccount.Id != 0)
                {
                    userAccount.IsEnable = result;
                    num = _efHelper.Modify<OA_UserAccount>(userAccount);

                }

                return num;

            }
            catch (Exception)
            {
                return -1;
            }

        }
        #endregion


        #region 讲员工数据表导入到Excel

        public bool EmployeeToExcel()
        {
            //try
            //{

            string[] ColumnsArray = new string[] { "编号", "账号", "姓名", "性别", "身份证", "户籍地", "现居住地", "出生日期", "职位", "部门" };
            var result = Common.ExcelHelper.ImportExcel(_efHelper.SqlQueryForDataTatable<dynamic>(@"SELECT UA.Id,UA.Account,UB.RealName,UB.Sex,UB.IDCard,UB.Address1,UB.Address2,UB.Brithday,
(SELECT Name FROM OA_Position P WHERE UB.PositionId=P.Id) AS PositionName,
(SELECT Name FROM OA_Department D WHERE UB.DepartmentId=D.Id) AS DepartmentName
 FROM OA_UserAccount UA LEFT JOIN OA_UserBasic UB ON UA.Id=UB.UserId"), ColumnsArray);
            return result;


            //}
            //catch (Exception ex)
            //{
            //    return false;
            //}
        }

        #endregion


        #region 查询本月入职员工人数列表
        /// <summary>
        /// 查询本月入职员工人数列表
        /// </summary>
        /// <param name="month">当前月份</param>
        /// <returns></returns>
        public static List<OA_UserBasic> QueryEmpListByMonth(int month)
        {
            try
            {
                return _efHelper.QueryList<OA_UserBasic>(u => u.InsertTime.Month == month).ToList();

            }
            catch (Exception ex)
            {
                return new List<OA_UserBasic>();
            }
        }
        #endregion


        #region 查询每个部门对应有多少人
        /// <summary>
        /// 查询每个部门对应有多少人
        /// </summary>
        /// <returns></returns>
        public static List<ExpandClass> QueryDepartmentCount()
        {
            try
            {
                var dt = _efHelper.SqlQueryForDataTatable<ExpandClass>(@"select d.Name,COUNT(*)as 'Num' from OA_UserBasic ub 
inner join OA_Department d on ub.DepartmentId=d.Id
group by d.Name");
                List<ExpandClass> list = new List<ExpandClass>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ExpandClass ec = new ExpandClass();
                    ec.name = dt.Rows[i]["Name"].ToString();
                    ec.y = int.Parse(dt.Rows[i]["Num"].ToString());
                    list.Add(ec);
                }

                return list;
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

    }

}
